Using excelize When Dealing With Excel

However, if our organization uses Excel, it can be more helpful to use its native format to write the data and supply visual representations of the data. excelize is a third-party Go package that can help us do that.

Note: The package can be found here on GitHub. Additionally, this is the link to Excelize's official documentation.

There is also an online version of Excel that is part of Microsoft's Office 365. We can manipulate spreadsheets directly there; however, we may find it easier to manipulate the spreadsheet offline and then import it.

If you are interested in the REST API, you can read about it here on Microsoft's website.

Creating a .xlsx file and adding some data#

Excel has a few characteristics that are helpful to understand:

• An Excel file has the .xlsx extension.

• Each .xlsx file contains sheets.

• Each sheet includes a set of rows and columns.

• A .xlsx file has a default sheet, called Sheet1.

• The intersection of a row and column is called a cell.

• Columns start with the letter A.

• Rows start with the number 1.

We are going to add some data that represents server data for a fictional fleet of devices. This includes the name of the server, the hardware generation, when it was acquired, and the CPU vendor. If we type ls in the terminal below, we should see the Book1.xlsx file.

The preceding code does the following:

• Line 10: It creates an Excel spreadsheet.

• Lines 13–16: It adds column labels.

• Line 18–25: It adds two servers, slvaa01 and slvac14.

• Line 28: It saves the Excel file.

There is a mustParse() function (used, but not defined above) that converts a string representing a date into time.Time. In Go, when we see must proceeding a function name, by convention, if the function encounters an error, it will panic.

This example is the simplest way to add data to a sheet. However, it is not very scalable. Let's create one that is scalable:

The preceding code does the following:

• Lines 1–6: It creates a serverSheet type for managing our Excel sheet.

• Lines 8–19: It has a constructor that adds our column labels.

Now we need something to add the data. If we type ls in the terminal below, we should see the Book1.xlsx file.

This code does the following:

• Line 72: It uses a lock to prevent multiple calls.

• Lines 75–86: It performs very basic data validation checks.

• Lines 88–93: It adds a row and then increments our internal nextRow counter.

Now we have a more scalable way to add data to our sheet. Next, let's discuss how to summarize data.

Data summarization#

There are two ways to summarize data in our sheet:

• Tracking summaries in our object

• Excel pivot tables

For our example, we use the first method. This method comes with several advantages:

• It is easier to implement.

• It performs faster calculations.

• It removes complex calculations from the spreadsheet.

However, it has a distinct disadvantage in that data changes do not affect the summary.

To track our data summary, let's add a struct type:

Let's modify the add() method that we wrote earlier to summarize our table:

The preceding code does the following:

• Lines 3–11: It looks at our vendor and adds to our summary counters.

• Lines 15–23: It adds a method to write our summaries to the sheet.

Next, let's discuss how we can add visualizations using this data.

Adding visualizations#

One of the reasons for using Excel over CSV for output is to add visualization elements. This allows us to quickly generate reports that users can look at that are more appealing than CSV and less intensive to write than web pages.

Adding a chart is done via the AddChart() method. AddChart() takes in a string representing JSON that indicates how to build the chart. In our example, we'll see a package, called chart, that extracts private types from excelize used to represent the charts and makes them public types. In this way, we can use a typed data structure instead of JSON that has been converted into that structure. This also allows for the easier discovery of values that we might wish to set:

This code does the following:

• Line 133: It creates a new 3D pie chart type.

• Lines 134–148: It sets the dimensions, title, and legend.

• Lines 149–163: It applies the chart values and categories.

• Line 165: It marshals the chart's instructions to JSON.

• Line 170: It calls AddChart to insert the chart into the sheet.

The Excel file generated looks like this:

So, we have covered the base minimum of using Excel for outputting reports. There are many other options, including inserting pictures, pivot tables, and advanced formatting directives. And while we wouldn't recommend Excel for data input into a system or a data storage format, it can be a useful data output system for summaries and viewing data.